import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_cfgdmx(event):
    date = event.get('p_date')
    if not date:
        date =datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_cfgdmx
            (
                select
                    *
                from
                (
                    select
                        if(zzfld00000b is null,'',zzfld00000b) as sn ,
                        1 as time_range,
                        ARRAY_JOIN(ARRAY_AGG(objectid), ',', '')   as id_details,
                        ARRAY_JOIN(ARRAY_AGG(p_date), ',', '')   as time_details,
                        ARRAY_JOIN(ARRAY_AGG(bsc), ',', '')   as area_details,
                        ARRAY_JOIN(ARRAY_AGG(fwst), ',', '')   as service_provider_details,
                        format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                    FROM
                        (
                            select
                                objectid,
                                max(zzfld00000b) zzfld00000b,
                                max(p_date) as p_date,
                                max(bsc) as bsc,
                                max(fwst) as fwst
                            from
                                ods.kyd_ods
                            where
                                date(p_date)>=(date('{date}') - interval '1' month)
                            and
                                date(p_date) < date('{date}')
                            and
                                 zzfld000001='QT30'
                            group by
                                objectid
                        ) t
                    group by
                        if(zzfld00000b is null,'',zzfld00000b)
                    having
                        count(objectid) >= 2
                )
                    UNION ALL
                (
                    select
                        if(zzfld00000b is null,'',zzfld00000b) as sn ,
                        3 as time_range,
                        ARRAY_JOIN(ARRAY_AGG(objectid), ',', '')   as id_details,
                        ARRAY_JOIN(ARRAY_AGG(p_date), ',', '')   as time_details,
                        ARRAY_JOIN(ARRAY_AGG(bsc), ',', '')   as area_details,
                        ARRAY_JOIN(ARRAY_AGG(fwst), ',', '')   as service_provider_details,
                        format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                    FROM
                        (
                            select
                                objectid,
                                max(zzfld00000b) zzfld00000b,
                                max(p_date) as p_date,
                                max(bsc) as bsc,
                                max(fwst) as fwst
                            from
                                ods.kyd_ods
                            where
                                date(p_date)>=(date('{date}') - interval '3' month)
                            and
                                date(p_date) < date('{date}')
                            and
                                 zzfld000001='QT30'
                            group by
                                objectid
                        ) t
                    group by
                        if(zzfld00000b is null,'',zzfld00000b)
                    having
                        count(objectid) >= 2
                )
                    UNION ALL
                (
                    select
                        if(zzfld00000b is null,'',zzfld00000b) as sn ,
                        6 as time_range,
                        ARRAY_JOIN(ARRAY_AGG(objectid), ',', '')   as id_details,
                        ARRAY_JOIN(ARRAY_AGG(p_date), ',', '')   as time_details,
                        ARRAY_JOIN(ARRAY_AGG(bsc), ',', '')   as area_details,
                        ARRAY_JOIN(ARRAY_AGG(fwst), ',', '')   as service_provider_details,
                        format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                    FROM
                        (
                            select
                                objectid,
                                max(zzfld00000b) zzfld00000b,
                                max(p_date) as p_date,
                                max(bsc) as bsc,
                                max(fwst) as fwst
                            from
                                ods.kyd_ods
                            where
                                date(p_date)>=(date('{date}') - interval '6' month)
                            and
                                date(p_date) < date('{date}')
                            and
                                 zzfld000001='QT30'
                            group by
                                objectid
                        ) t
                    group by
                        if(zzfld00000b is null,'',zzfld00000b)
                    having
                        count(objectid) >= 2
                )
                    UNION ALL
                (
                    select
                        if(zzfld00000b is null,'',zzfld00000b) as sn ,
                        12 as time_range,
                        ARRAY_JOIN(ARRAY_AGG(objectid), ',', '')   as id_details,
                        ARRAY_JOIN(ARRAY_AGG(p_date), ',', '')   as time_details,
                        ARRAY_JOIN(ARRAY_AGG(bsc), ',', '')   as area_details,
                        ARRAY_JOIN(ARRAY_AGG(fwst), ',', '')   as service_provider_details,
                        format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                    FROM
                        (
                            select
                                objectid,
                                max(zzfld00000b) zzfld00000b,
                                max(p_date) as p_date,
                                max(bsc) as bsc,
                                max(fwst) as fwst
                            from
                                ods.kyd_ods
                            where
                                date(p_date)>=(date('{date}') - interval '12' month)
                            and
                                date(p_date) < date('{date}')
                            and
                                 zzfld000001='QT30'
                            group by
                                objectid
                        ) t
                    group by
                        if(zzfld00000b is null,'',zzfld00000b)
                    having
                        count(objectid) >= 2
                )
            )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_cfgdmx(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

